#include "database.h"
#include <QSqlDatabase>
#include <QSqlQuery>
#include<QMessageBox>
#include<QDebug>

DataBase::DataBase(QObject *parent)
    : QObject{parent}
{
    db=QSqlDatabase::addDatabase("QMYSQL");
    init();
}
DataBase::~DataBase(){//析构函数

}

DataBase& DataBase::getInstance(){//单例模式
    static DataBase database;
    return  database;
}


void DataBase::init(){//数据库初始化

    // db.setDatabaseName("chemicaldatabase");
    db.setDatabaseName("shj");
    db.setHostName("127.0.0.1");
    db.setUserName("root");
    db.setPassword("20021113xx");
    db.setPort(3306);
    if(db.open()==false){
        qDebug("数据库连接失败");
    }else{
        qDebug("数据库连接成功");
    }
}


QVector<QVector<double>> DataBase::getExitPos(){//获取安全出口位置
    QVector<QVector<double>> ans;
    QSqlQuery sql;
    QString s="select posX,posY,posZ from road where `exit`=1";
    sql.exec(s);
    while(sql.next()){//获取所有安全出口的位置
        QVector<double> tmp;
        tmp.push_back(sql.value(0).toDouble());
        tmp.push_back(sql.value(1).toDouble());
        tmp.push_back(sql.value(2).toDouble());
        ans.push_back(tmp);
    }
    return ans;
}

QVector<QVector<double>> DataBase::getPointData(){//获取所有节点位置
    QVector<QVector<double>> ans;
    QSqlQuery sql;
    QString s="select * from road";
    sql.exec(s);
    while(sql.next()){//获取所有节点的位置
        QVector<double> tmp;
        tmp.push_back(sql.value(1).toDouble());
        tmp.push_back(sql.value(2).toDouble());
        tmp.push_back(sql.value(3).toDouble());
        ans.push_back(tmp);
    }
    return ans;
}

QVector<double> DataBase::getPointData(int i){//获取第i个节点位置
    QVector<double> tmp;
    QSqlQuery sql;
    QString s="select * from road where `num`="+QString::number(i);
    sql.exec(s);
    while(sql.next()){//获取指定节点的位置

        tmp.push_back(sql.value(1).toDouble());
        tmp.push_back(sql.value(2).toDouble());
        tmp.push_back(sql.value(3).toDouble());

    }
    return tmp;
}

/*
 * 获取所有边集
*/
QList<DataBase::edge> DataBase::getEdgeData(){

    QString s="select * from edge";
    QSqlQuery sql;
    sql.exec(s);
    QList<edge> ans;
    while(sql.next()){
        //QVector<double> tmp;
        edge new_edge{sql.value(0).toInt(), sql.value(1).toInt(), sql.value(2).toFloat()};
        ans.append(new_edge);
    }
    return ans;
}

/*
 * 获取指定消防员的路径信息
 * return   返回长度为2的数组，第一个表示总路径长度，第二个表示剩余路径长度
 */
QList<double> DataBase::getFireManinfo(int i){
    QString s="select totaldistance,distance from fireman_information where number="+QString::number(i);
    QSqlQuery sql;
    sql.exec(s);
    QList<double> ans;
    while(sql.next()){
        ans.append(sql.value(0).toDouble());
        ans.append(sql.value(1).toDouble());
    }
    qDebug()<<ans;
    return ans;
}

/*
 * 将数据写入数据库
 * table:表名
 * value:写入的值
*/
void DataBase::writeData(QString table,QString value){
    QSqlQuery sql;
    if(table=="fireman1_savepath"){
        //插入新的路径
        QString s="insert into fireman1_savepath(坐标) values('"+value+"');";
        sql.exec(s);
    }
}

/*
 * 修改数据库数据
 * table:表名
 * value:写入的值
*/
void DataBase::updateData(QString table,QString key,QString value){
    QSqlQuery sql;
    if(table=="fireman_information"){
        //插入新的路径
        QString s="update fireman_information set "+key+"="+value+"where number=1";
        bool t=sql.exec(s);
        if(t)
            qDebug()<<"更新成功";
        else
            qDebug()<<"更新失败";
    }
}


/*
 * 清空数据库
 * table:表名
*/
void DataBase::clearData(QString table){
    QSqlQuery sql;
    QString s="delete from "+table;
    bool t=sql.exec(s);
    if(t)
        qDebug()<<"删除成功";
    else
        qDebug()<<"删除失败";
}


/*
 * 如果数据存在则修改，若不存在则插入
 * table:表名
 * map:修改的键值对
 * name1:唯一标识符（主键）
*/
bool DataBase::handerData(QString table,QMap<QString,QString>& map,QString name1){
    QSqlQuery sql;
    QString s1=QString("select * from %1 where %2='%3'").arg(table).arg(name1).arg(map[name1]);
    qDebug()<<s1;
    bool t=sql.exec(s1);
    if(sql.next()!=NULL){//存在就修改
        s1="update "+table+" set ";
        for(auto it=map.begin();it!=map.end();it++)
            if(it.key()!=name1){
                s1=s1+it.key()+"='"+it.value()+"'";
                if((it+1)!=map.end()) s1=s1+",";
            }
        s1=s1+" where "+name1+"='"+map[name1]+"'";
        qDebug()<<s1;
    }else{//不存在就插入
        s1="insert into "+table+" (";
        QString s2="values(";
        for(auto it=map.begin();it!=map.end();it++){
            s1=s1+it.key();
            s2=s2+it.value();
            if((it+1)!=map.end()){
                s1=s1+",";

                s2="'"+s2+"'"+",";
            }
        }
        s1=s1+")"+s2+")";
        qDebug()<<s1;
    }
    t=sql.exec(s1);
    return t;
}

/*
 * 由起始点和终点获取边
 * from:起始点
 * to: 终点
 * return   返回边结构体
 */
DataBase::edge DataBase::getEdgeData(int from,int to){
    QString s=QString("select * from edge where (begin=%1 and end=%2) or (begin=%3 and end=%4)").arg(from).arg(to).arg(to).arg(from);
    QSqlQuery sql;
    sql.exec(s);
    edge ans;
    while(sql.next()){
        ans={sql.value(0).toInt(), sql.value(1).toInt(), sql.value(2).toFloat(),sql.value(3).toFloat(),sql.value(4).toFloat()};
    }
    return ans;
}



